package week06;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Scanner;

public class week06 {
static Scanner input = new Scanner(System.in);
	
	static final String URL = "jdbc:mariadb://localhost:3306/books";
	static final String USER = "zqc";
	static final String PWD = "123456";
	
	public static void main(String[] args) {
		try {
			Class.forName("org.mariadb.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			

			System.exit(0);
		}
		
		System.out.println("查询书籍：");
		System.out.println("1. 按ID查询");
		System.out.println("2. 按书名关键字查询");
		System.out.println("3. 按价格区间查询");
		System.out.println("4. 按出版年份查询");
		System.out.println("5. 按出版社名称查询");
		System.out.println("0. 退出");
		System.out.println("----------------");
		System.out.print("请选择：");
		
		int choice = Integer.parseInt(input.nextLine());
		
		switch (choice) {
		case 0:
			return;
		case 1:
			queryByBookId();
			break;
		case 2:
			queryByTitle();
			break;
		case 3:
			queryByPrices();
			break;
		case 4:
			queryByYearPublished();
			break;
		case 5:
			queryByPublisherName();
			break;
		default:
			System.out.println("输入错误");
			break;
		}

	}

	private static void queryByBookId() {
		
		System.out.print("书籍ID：");
		int bookId = input.nextInt();
		
		final String SQL = "SELECT id, title, price, date_published, publisher_id FROM book WHERE id = ?";
		

		try (Connection con = DriverManager.getConnection(URL, USER, PWD);
				PreparedStatement pstmt = con.prepareStatement(SQL)) {
			
			
			pstmt.setInt(1, bookId);
			
			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					String title = rs.getString(2); 
					double price = rs.getDouble(3);
					Date datePublished = rs.getDate(4);
					int publisherId = rs.getInt(5);
					System.out.printf("书名：%s，单价：%.2f，出版日期：%s, 出版社ID：%d%n",
							title, 
							price,
							datePublished,
							publisherId);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	private static void queryByTitle() {
		System.out.print("书名关键字：");
		String keyword = input.nextLine();
		
		final String SQL = "SELECT id, title, price, date_published, publisher_id FROM book WHERE title LIKE ?";
		
		try (Connection con = DriverManager.getConnection(URL, USER, PWD);
				PreparedStatement pstmt = con.prepareStatement(SQL)) {
			
			pstmt.setString(1, "%" + keyword + "%");
			
			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					String title = rs.getString(2); 
					double price = rs.getDouble(3);
					Date datePublished = rs.getDate(4);
					int publisherId = rs.getInt(5);
					System.out.printf("书名：%s，单价：%.2f，出版日期：%s, 出版社ID：%d%n",
							title, 
							price,
							datePublished,
							publisherId);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	private static void queryByPrices() {
		System.out.print("价格下限：");
		double minprice=input.nextDouble();
		System.out.print("价格上限：");
		double maxprice=input.nextDouble();
		
		final String SQL = "SELECT id, title, price, date_published, publisher_id FROM book WHERE price>="+ minprice+"and price<="+maxprice;
		try (Connection con = DriverManager.getConnection(URL, USER, PWD);
				PreparedStatement pstmt = con.prepareStatement(SQL)) {
			
			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					String title = rs.getString(2); 
					double price = rs.getDouble(3);
					Date datePublished = rs.getDate(4);
					int publisherId = rs.getInt(5);
					System.out.printf("书名：%s，单价：%.2f，出版日期：%s, 出版社ID：%d%n",
							title, 
							price,
							datePublished,
							publisherId);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static void queryByYearPublished() {
		System.out.print("出版年份：");
		int year=input.nextInt();
		final String SQL = "SELECT id, title, price, date_published, publisher_id FROM book WHERE date_published = ?";
		
		try (Connection con = DriverManager.getConnection(URL, USER, PWD);
				PreparedStatement pstmt = con.prepareStatement(SQL)) {
			
			
			pstmt.setInt(1, year);
			
			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					String title = rs.getString(2); 
					double price = rs.getDouble(3);
					Date datePublished = rs.getDate(4);
					int publisherId = rs.getInt(5);
					System.out.printf("书名：%s，单价：%.2f，出版日期：%s, 出版社ID：%d%n",
							title, 
							price,
							datePublished,
							publisherId);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	private static void queryByPublisherName() {
		System.out.print("出版社名：");
		String publisherName = input.nextLine();
		
		final String SQL = "SELECT id, title, price, date_published, name FROM book_details WHERE name = ?";
		
		try (Connection con = DriverManager.getConnection(URL, USER, PWD);
				PreparedStatement pstmt = con.prepareStatement(SQL)) {
			
			pstmt.setString(1, publisherName);
			
			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					String title = rs.getString(2); 
					double price = rs.getDouble(3);
					Date datePublished = rs.getDate(4);
					String name = rs.getString(5);
					System.out.printf("书名：%s，单价：%.2f，出版日期：%s, 出版社：%s%n",
							title, 
							price,
							datePublished,
							name);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	
	

}
